using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

using PHSRAG.Insight21.BusinessObjects;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using PHSRAG.Utility;

namespace PHSRAG.Insight21.ClinicalTrials
{
   /// <summary>
   /// The BudgetVisits class represents a collection of BudgetVisit objects.  It is 
   /// responsible for loading and saving the Budget Visits
   /// </summary>
   public class BudgetVisits : SaveableBusinessObject, IEnumerable
   {

      #region Constants
      private const string BudgetVisitsKey = "BudgetVisits_0A7C68A2-2BFA-4e01-9207-141743F8225A";
      private const string TableName = "BudgetVisits";
      #endregion

      #region Instance Variables
      private ArrayList budgetVisits = new ArrayList( );
      private Budget budget;
      private SqlCommand updateBudgetVisitsCommand;
      private SqlCommand deleteBudgetVisitsCommand;
      #endregion

      #region Constructor
      /// <summary>
      /// Initialize the base class with the specified cache policy and create the data container.
      /// </summary>
      public BudgetVisits( ICachePolicy cachePolicy ) : base ( cachePolicy )
      {
         DataContainer = new DataSet( );
         budget = (Budget)CachePolicy.GetCachedObject( Budget.CacheKey );
      }
      #endregion
   
      #region Base Methods
      /// <summary>
      /// Cache this instance using the prevalent cache policy.
      /// </summary>
      public override void Cache( )
      {
         CachePolicy.Cache( BudgetVisitsKey, this );
      }

      /// <summary>
      /// Remove this instance from the cache using the prevalent cache policy.
      /// </summary>
      public override void UnCache( )
      {
         CachePolicy.UnCache( BudgetVisitsKey );
      }

      /// <summary>
      /// Load visit details pertaining to a specific budget.
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Object array which must contain the budget ID.</param>
      public override void Load( string connectionString, params object[] args )
      {
         if ( args.Length != 1 )
            throw new Exception("BudgetVisits load requires a budget id.");
			
         DataAccess.LoadDataSet( DataContainer, TableName, connectionString, "GetClinicalTrialBudgetVisits", 
            Utility.DataAccess.BuildSqlParameterArray( "@budgetId", SqlDbType.Int, args[0] ) );

         if ( DataContainer.Tables.Count == 0 )
            throw new Exception("Failed to load Budget Visit information.");

         foreach( DataRow budgetVisitRow in DataContainer.Tables[TableName].Rows )
         {
            budgetVisits.Add( new BudgetVisit( CachePolicy, budgetVisitRow ) );
         }
      }

      /// <summary>
      /// Saves visit details associated with this budget.  
      /// </summary>
      /// <param name="connectionString">Connection string to the database</param>
      /// <param name="args">Optional parameters (ignored)</param>
      public override void Save( string connectionString, params object[] args )
      {
         SqlConnection connection = new SqlConnection( connectionString );
         SqlTransaction transaction = null;
         try
         {
            connection.Open( );
            transaction = connection.BeginTransaction( );
            BuildCommands( );
            updateBudgetVisitsCommand.Connection = connection;
            updateBudgetVisitsCommand.Transaction = transaction;
            deleteBudgetVisitsCommand.Connection = connection;
            deleteBudgetVisitsCommand.Transaction = transaction;

            SqlDataAdapter adapter = new SqlDataAdapter( );
            adapter.InsertCommand = updateBudgetVisitsCommand;
            adapter.UpdateCommand = updateBudgetVisitsCommand;
            adapter.DeleteCommand = deleteBudgetVisitsCommand;
            adapter.Update( DataContainer, TableName );
            transaction.Commit( );
         }
         catch( Exception e )
         {
            transaction.Rollback( );
            throw(new Exception(@"Error saving Budget Visits to the database.", e));
         }
         finally
         {
            if ( connection.State != ConnectionState.Closed )
               connection.Close( );
            transaction.Dispose( );
         }
      }
      #endregion

      #region Private Methods
      private void BuildCommands( )
      {
         if ( updateBudgetVisitsCommand == null )
         {
            updateBudgetVisitsCommand = new SqlCommand( );
            updateBudgetVisitsCommand.CommandText = "UpdateClinicalTrialBudgetVisits";
            updateBudgetVisitsCommand.CommandType = CommandType.StoredProcedure;
			updateBudgetVisitsCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			SqlParameter paramId = new SqlParameter( "@budgetVisitId", SqlDbType.Int );
            paramId.SourceColumn = "Id";
            paramId.Direction = ParameterDirection.InputOutput;
            updateBudgetVisitsCommand.Parameters.Add( paramId );
            updateBudgetVisitsCommand.Parameters.Add( "@budgetId", SqlDbType.Int ).SourceColumn = "ClinicalTrialBudgetId";
            updateBudgetVisitsCommand.Parameters.Add( "@visitNumber", SqlDbType.Int ).SourceColumn = "VisitNumber";
            updateBudgetVisitsCommand.Parameters.Add( "@visitDescription", SqlDbType.VarChar ).SourceColumn = "VisitDescription";
         }

         if ( deleteBudgetVisitsCommand == null )
         {
            deleteBudgetVisitsCommand = new SqlCommand( );
            deleteBudgetVisitsCommand.CommandText = "DeleteClinicalTrialBudgetVisits";
            deleteBudgetVisitsCommand.CommandType = CommandType.StoredProcedure;
			deleteBudgetVisitsCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
			SqlParameter paramDeleteId = new SqlParameter( "@budgetVisitId", SqlDbType.Int );
            paramDeleteId.SourceColumn = "Id";
            paramDeleteId.Direction = ParameterDirection.Input;
            deleteBudgetVisitsCommand.Parameters.Add( paramDeleteId );
         }
      }
      #endregion

      #region IEnumerable Methods
      /// <summary>
      /// Gets an IEnumerator interface.  Required for implementation of IEnumerable
      /// </summary>
      /// <returns>IEnumerator</returns>
      public IEnumerator GetEnumerator( )
      {
		   return budgetVisits.GetEnumerator( );
      }
      #endregion

      #region Public Collection helpers
      /// <summary>
      /// The Count property returns the number of items in the Budget Visit collection
      /// </summary>
      public int Count
      {
         get
         {
            return budgetVisits.Count;
         }
      }

      /// <summary>
      /// Indexer to get a specific Budget Visit from the collection
      /// </summary>
      public BudgetVisit this[int index]
      {
         get
         {
            return (BudgetVisit)budgetVisits[ index ];
         }
      }
      #endregion

      #region Public Methods
      /// <summary>
      /// Creates a new BudgetVist and adds the item to the collection.  Also sets default
      /// properties on the BudgetVisit.
      /// </summary>
      /// <returns>BudgetVisit</returns>
      public BudgetVisit CreateNewBudgetVisit( )
      {
         DataRow newRow = DataContainer.Tables[TableName].NewRow( );
         DataContainer.Tables[TableName].Rows.Add( newRow );
         BudgetVisit newBudgetVisit = new BudgetVisit( CachePolicy, newRow );
         newBudgetVisit.BudgetId = budget.Id;
         budgetVisits.Add( newBudgetVisit );
		   return newBudgetVisit;
      }

      /// <summary>
      /// Removes a specific BudgetVisit from the collection.  Must call save on BudgetVisits
      /// to save this change to the database.
      /// </summary>
      /// <param name="budgetExpense">BudgetExpense to remove from the collection.</param>
      public void RemoveBudgetVisit( BudgetVisit budgetVisit )
      {
         DataContainer.Tables[TableName].Select( string.Format( "{0}{1}", "Id = ", budgetVisit.Id ) )[0].Delete( );
         budgetVisits.Remove( budgetVisit );
      }
      #endregion

   }// END CLASS DEFINITION BudgetVisits
}